package com.nit.lab.vote.mapper.vote;

import com.nit.lab.vote.dto.vote.CandidateDTO;
import com.nit.lab.vote.entity.vote.CPEntity;
import com.nit.lab.vote.entity.vote.CandidateEntity;
import com.nit.lab.vote.entity.vote.UPEntity;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface CandidateMapper {

    @Select({"<script> SELECT *, candidate_id AS `key` " +
            "FROM candidate " +
            "WHERE 1 = 1 " +
            "<if test = 'candidateName != null'> AND candidate_name LIKE CONCAT('%', #{candidateName}, '%') </if>" +
            "ORDER BY" +
            "<if test = 'sortField != null'>" +
            "${sortField} ${sortOrder}, </if>" +
            "gmt_create DESC " +
            "</script>"})
    List<CandidateDTO> list4Table(
            @Param("candidateName") String candidateName,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder
    ) throws Exception;

    @Select("<script>SELECT count(*) FROM candidate " +
            "WHERE true " +
            "<if test = 'candidateName != null'> AND candidate_name LIKE CONCAT('%', #{candidateName}, '%') </if>" +
            "</script>")
    Long count4Table1(
            @Param("candidateName") String candidateName
    ) throws Exception;

    @Insert("Insert into candidate " +
            "(candidate_name, employee_number, id_number, " +
            "dept, type, subject, gmt_create) " +
            "values " +
            "(#{entity.candidateName}, #{entity.employeeNumber}, #{entity.idNumber}, " +
            "#{entity.dept}, #{entity.type}, #{entity.subject}, #{entity.gmtCreate})")
    @Options(useGeneratedKeys = true, keyProperty = "candidateId", keyColumn = "candidate_id")
    Integer add(@Param("entity") CandidateEntity candidateEntity) throws Exception;

    @Update("UPDATE candidate " +
            "SET candidate_name = #{entity.candidateName}, dept = #{entity.dept}, " +
            "employee_number = #{entity.employeeNumber}, id_number = #{entity.idNumber}, " +
            "type = #{entity.type}, subject = #{entity.subject}" +
            "WHERE candidate_id = #{entity.candidateId}")
    Integer update(@Param("entity") CandidateEntity candidateEntity) throws Exception;

    @Delete("DELETE FROM candidate " +
            "WHERE candidate_id = #{candidateId}")
    Integer remove(@Param("candidateId") Long candidateId) throws Exception;

    @Select("SELECT *, candidate_id AS `key` " +
            "FROM candidate " +
            "WHERE candidate_id = #{candidateId} LIMIT 1")
    CandidateDTO getCandidateById(@Param("candidateId") Long candidateId) throws Exception;



    @Select({"<script> SELECT *, cp.candidate_project_id AS `key` " +
            "FROM candidate c " +
            "JOIN candidate_project cp ON c.candidate_id = cp.candidate_id " +
            "WHERE 1 = 1 " +
            "<if test = 'projectId != null'> AND project_id = #{projectId}</if> " +
            "<if test = 'candidateName != null'> AND candidate_name LIKE CONCAT('%', #{candidateName}, '%') </if>" +
            "<if test = 'dept != null'> AND dept LIKE CONCAT('%', #{dept}, '%') </if>" +
            "ORDER BY " +
            "<if test = 'sortField != null'>" +
            "${sortField} ${sortOrder}, </if>" +
            "gmt_create DESC " +
            "</script>"})
    List<CandidateDTO> listByProject(
            @Param("candidateName") String candidateName,
            @Param("dept") String dept,
            @Param("projectId") Long projectId,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder
    ) throws Exception;

    @Select("<script>SELECT count(*) " +
            "FROM candidate c " +
            "JOIN candidate_project cp ON c.candidate_id = cp.candidate_id " +
            "WHERE true " +
            "<if test='projectId != null'> AND cp.project_id = #{projectId} </if> " +
            "<if test = 'candidateName != null'> AND candidate_name LIKE CONCAT('%', #{candidateName}, '%') </if>" +
            "<if test = 'dept != null'> AND dept LIKE CONCAT('%', #{dept}, '%') </if>" +
            "</script>")
    Long countByProject(
            @Param("candidateName") String candidateName,
            @Param("dept") String dept,
            @Param("projectId") Long projectId
    ) throws Exception;

    @Select({"<script> SELECT *, c.candidate_id AS `key`, n.project_id AS `projectId` " +
            "FROM candidate c " +
            "Left JOIN ( select * from candidate_project cp " +
            "WHERE 1 = 1 " +
            "<if test = 'projectId != null'> AND project_id = #{projectId}</if> " +
            " ) n on c.candidate_id = n.candidate_id " +
            "where 1=1 " +
            "<if test = 'candidateName != null'> And candidate_name LIKE CONCAT('%', #{candidateName}, '%') </if> " +
            "Order by " +
            "<if test = 'sortField != null'>" +
            "${sortField} ${sortOrder}, </if> " +
            "n.project_id DESC, c.candidate_id DESC " +
            "</script>"})
    List<CandidateDTO> listAllProject(
            @Param("candidateName") String candidateName,
            @Param("projectId") Long projectId,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder
    ) throws Exception;

    @Select("<script>SELECT count(*) " +
            "FROM candidate c " +
            "WHERE true " +
            "<if test = 'candidateName != null'> AND candidate_name LIKE CONCAT('%', #{candidateName}, '%') </if> " +
            "</script>")
    Long countAllProject(
            @Param("candidateName") String candidateName
    ) throws Exception;

    @Insert("Insert into candidate_project " +
            "(candidate_id, project_id) " +
            "values " +
            "(#{entity.candidateId}, #{entity.projectId})")
    @Options(useGeneratedKeys = true, keyProperty = "candidateProjectId", keyColumn = "candidate_project_id")
    Integer addByProject(@Param("entity") CPEntity cpEntity) throws Exception;

    @Delete("DELETE FROM candidate_project " +
            "WHERE project_id = #{projectId} " +
            "and candidate_id = #{candidateId} " +
            "limit 1")
    Integer removeByProject(
            @Param("projectId") Long projectId,
            @Param("candidateId") Long candidateId) throws Exception;



    @Select("SELECT candidate_project_id from candidate_project " +
            "WHERE project_id = #{projectId} AND candidate_id = #{candidateId} LIMIT 1")
    Long getCPId(@Param("projectId") Long projectId,
                 @Param("candidateId") Long candidateId) throws Exception;

    @Insert("INSERT INTO cp_file " +
            "(candidate_project_id, file) " +
            "VALUES " +
            "(#{cpId}, #{file})")
    Integer addFile(@Param("cpId") Long cpId,
                    @Param("file") String file) throws Exception;

    @Delete("DELETE FROM cp_file " +
            "WHERE file = #{file}")
    Integer removeFile(@Param("file") String file) throws Exception;

    @Select("SELECT file from cp_file " +
            "WHERE candidate_project_id = #{cpId}")
    List<String> getFiles(@Param("cpId") Long cpId) throws Exception;

    @Select("SELECT file " +
            "FROM cp_file " +
            "WHERE candidate_project_id = #{cpId} ")
    List<String> getFilesByCPId(
            @Param("cpId") Long cpId) throws Exception;


}
